from src.main.server.mapper.CreateMysqlCon import CreateMysqlCon


class VolInfoImpl:

    def __init__(self):
        self.con = CreateMysqlCon().get_con()
        if not self.con:
            raise ConnectionError("数据库连接失败，请检查配置或网络")
        self.cursor = self.con.cursor()

    def all_info(self):
        """查询所有志愿者信息（包括技能和服务记录）"""
        SQL = ("SELECT v.username, v.age, v.gender, v.pho_num, "
            "(SELECT COUNT(*) FROM server_records AS sr WHERE sr.vol_id = v.ID) AS record_count,"
            "v.score "
            "FROM volunteers_info AS v")
        self.cursor.execute(SQL)
        return self.cursor.fetchall()

    def get_info(self, data):
        """查询回显"""
        SQL = ("select username, password, age, gender, pho_num, score "
               "from volunteers_info "
               "where username = %s and age = %s")
        self.cursor.execute(SQL, data)
        return self.cursor.fetchall()

    def update(self, data):
        SQL = ("update volunteers_info "
               "set username = %s, "
               "password = %s, "
               "age = %s, "
               "gender = %s, "
               "pho_num = %s, "
               "score = %s "
               "where username = %s")
        self.cursor.execute(SQL, data)

    def delete(self, data):
        SQL = ("delete from volunteers_info "
               "where username = %s")
        self.cursor.execute(SQL, data)

    def add_score(self, data):
        SQL = ("update volunteers_info "
               "set score = score + %s "
               "where username = %s")
        self.cursor.execute(SQL, data)


if __name__ == '__main__':
    impl_instance = VolInfoImpl()
    result = impl_instance.all_info()
    print(result)